﻿Imports System.Data
Imports System.Data.SqlClient
Imports Entidad

Public Class DocumentoClienteDatos

    Sub actualizarEstadoPedido(ByVal codPedido As String, ByVal estado As String, tipoDOC As String)
        Dim conex As New Conexion
        Dim coneccion As SqlConnection = conex.conectar()
        coneccion.Open()
        Dim query As String = "UPDATE Documento_Cliente " &
                              "SET DCI_Estado = @est " &
                              "WHERE DCI_Codigo = @codigo " &
                              "AND TDOC_Codigo = @tipo"

        Dim comando As New SqlCommand
        With comando
            .Connection = coneccion
            .CommandText = query
            .Parameters.AddWithValue("@est", estado)
            .Parameters.AddWithValue("@codigo", codPedido)
            .Parameters.AddWithValue("@tipo", tipoDOC)
            .ExecuteNonQuery()
        End With
        coneccion.Close()
    End Sub

    Function obtenerEstadoPedido(codPedido As String) As DataSet
        Dim conex As New Conexion
        Dim coneccion As SqlConnection = conex.conectar()
        coneccion.Open()
        Dim query As String = "SELECT DCI_Estado,DCI_Observaciones FROM Documento_Cliente WHERE DCI_Codigo = '" & codPedido & "'"

        Dim adapter As New SqlDataAdapter(query, coneccion)
        Dim ds As New DataSet
        adapter.Fill(ds)

        coneccion.Close()

        Return ds
    End Function

    Function listarPedidos(tipoDOC As String) As DataSet
        Dim conex As New Conexion
        Dim coneccion As SqlConnection = conex.conectar()
        coneccion.Open()
        Dim query As String = "SELECT c.CLI_Codigo, p.DCI_Codigo as NroPedido, cc.CAC_Descripcion AS TipCliente, CONVERT(CHAR(10),p.DCI_Fecha,103) AS FRegistro " &
                              "FROM Documento_Cliente p " &
                              "INNER JOIN cliente c ON p.CLI_Codigo = c.CLI_Codigo " &
                              "INNER JOIN CategoriaCliente cc ON cc.CAC_Codigo = c.CAC_Codigo AND cc.CAC_Activo = '1' " &
                              "WHERE p.DCI_Estado = '01' AND TDOC_Codigo = '" & tipoDOC & "'"

        Dim adapter As New SqlDataAdapter(query, coneccion)
        Dim ds As New DataSet
        adapter.Fill(ds)
        coneccion.Close()

        Return ds
    End Function

    Function verificarPedidoAnuladoSQL(CIA As String, SUC As String, pedCodigo As String, tipoDOC As String) As Boolean
        Dim valor As Boolean = False
        Dim conex As New Conexion
        Dim coneccion As SqlConnection = conex.conectar()
        coneccion.Open()
        Dim query As String = "SELECT DCI_Estado " &
                              "FROM Documento_Cliente " &
                              "WHERE CIA = '" & CIA & "' AND SUC_Codigo = '" & SUC & "' " &
                              "AND DCI_Codigo = '" & pedCodigo & "' " &
                              "AND TDOC_Codigo = '" & tipoDOC & "'"

        Dim adapter As New SqlDataAdapter(query, coneccion)
        Dim ds As New DataSet
        adapter.Fill(ds)
        coneccion.Close()

        If ds.Tables(0).Rows(0)("DCI_Estado").ToString.Trim = "17" Then
            valor = True
        End If

        Return valor
    End Function

    Function verificarPedidoIngresadoSQL(CIA As String, SUC As String, pedCodigo As String, tipoDOC As String) As Boolean
        Dim valor As Boolean = False
        Dim conex As New Conexion
        Dim coneccion As SqlConnection = conex.conectar()
        coneccion.Open()
        Dim query As String = "SELECT DCI_Estado " &
                              "FROM Documento_Cliente " &
                              "WHERE CIA = '" & CIA & "' AND SUC_Codigo = '" & SUC & "' " &
                              "AND DCI_Codigo = '" & pedCodigo & "' " &
                              "AND TDOC_Codigo = '" & tipoDOC & "'"

        Dim adapter As New SqlDataAdapter(query, coneccion)
        Dim ds As New DataSet
        adapter.Fill(ds)
        coneccion.Close()

        If ds.Tables(0).Rows(0)("DCI_Estado").ToString.Trim = "02" Then
            valor = True
        End If

        Return valor
    End Function

    Function obtenerBandejasXPedido(codPedido As String, tipo As String) As DataSet
        Dim conex As New Conexion
        Dim coneccion As SqlConnection = conex.conectar()
        coneccion.Open()
        Dim query As String = ""

        If tipo = "" Then
            query = "SELECT DISTINCT(dadpub.BAN_Codigo) AS Bandejas " &
                    "FROM Documento_Cliente dc " &
                    "INNER JOIN Documento_Cliente_Detalle dcd " &
                    "ON dc.DCI_Codigo = dcd.DCI_Codigo AND dcd.DCD_Activo = '1' " &
                    "INNER JOIN Documento_Cliente_Almacen_Detalle dcad " &
                    "ON dcad.DCI_Codigo = dcd.DCI_Codigo AND dcad.DCD_Codigo = dcd.DCD_Codigo AND dcad.DCAD_Activo = '1' " &
                    "INNER JOIN Documento_Almacen_Detalle dad " &
                    "ON dcad.DCA_Codigo = dad.DCA_Codigo AND dcad.DAD_Codigo = dad.DAD_Codigo AND dad.DAD_Activo = '1' " &
                    "INNER JOIN Documento_Almacen_Detalle_Pickeador dadp " &
                    "ON dadp.DCA_Codigo = dad.DCA_Codigo AND dadp.DAD_Codigo = dad.DAD_Codigo AND dadp.DADP_Activo = '1' " &
                    "INNER JOIN Documento_Almacen_Detalle_Pickeador_Ubicacion dadpu " &
                    "ON dadpu.DCA_Codigo = dadp.DCA_Codigo AND dadpu.DADP_Codigo = dadp.DADP_Codigo AND dadpu.DADPU_Activo = '1' " &
                    "INNER JOIN Documento_Almacen_Detalle_Pickeador_Ubicacion_Bandeja dadpub " &
                    "ON dadpub.DADPU_Codigo = dadpu.DADPU_Codigo AND dadpub.DADPUB_Activo = '1' " &
                    "WHERE dc.DCI_Codigo = '" & codPedido & "' AND dc.DCI_Estado = '07'"
        Else
            query = "SELECT DISTINCT(dadpub.BAN_Codigo) AS Bandejas " &
                    "FROM Documento_Cliente dc " &
                    "INNER JOIN Documento_Cliente_Detalle dcd " &
                    "ON dc.DCI_Codigo = dcd.DCI_Codigo AND dcd.DCD_Activo = '1' " &
                    "INNER JOIN Documento_Cliente_Almacen_Detalle dcad " &
                    "ON dcad.DCI_Codigo = dcd.DCI_Codigo AND dcad.DCD_Codigo = dcd.DCD_Codigo AND dcad.DCAD_Activo = '1' " &
                    "INNER JOIN Documento_Almacen_Detalle dad " &
                    "ON dcad.DCA_Codigo = dad.DCA_Codigo AND dcad.DAD_Codigo = dad.DAD_Codigo AND dad.DAD_Activo = '1' " &
                    "INNER JOIN Documento_Almacen_Detalle_Pickeador dadp " &
                    "ON dadp.DCA_Codigo = dad.DCA_Codigo AND dadp.DAD_Codigo = dad.DAD_Codigo AND dadp.DADP_Activo = '1' " &
                    "INNER JOIN Documento_Almacen_Detalle_Pickeador_Ubicacion dadpu " &
                    "ON dadpu.DCA_Codigo = dadp.DCA_Codigo AND dadpu.DADP_Codigo = dadp.DADP_Codigo AND dadpu.DADPU_Activo = '1' " &
                    "INNER JOIN Documento_Almacen_Detalle_Pickeador_Ubicacion_Bandeja dadpub " &
                    "ON dadpub.DADPU_Codigo = dadpu.DADPU_Codigo AND dadpub.DADPUB_Activo = '1' " &
                    "WHERE dc.DCI_Codigo = '" & codPedido & "'"
        End If

        Dim adapter As New SqlDataAdapter(query, coneccion)
        Dim ds As New DataSet
        adapter.Fill(ds)

        coneccion.Close()

        Return ds
    End Function

    Sub actualizarFlagProgramado(ByVal codPedido As String, tipoDOC As String, flag As String)
        Dim conex As New Conexion
        Dim coneccion As SqlConnection = conex.conectar()
        coneccion.Open()
        Dim query As String = "UPDATE Documento_Cliente " &
                              "SET DCI_Programado = @FLAG " &
                              "WHERE DCI_Codigo = @DCI " &
                              "AND TDOC_Codigo = @TDOC"

        Dim comando As New SqlCommand
        With comando
            .Connection = coneccion
            .CommandText = query
            .Parameters.AddWithValue("@FLAG", flag)
            .Parameters.AddWithValue("@DCI", codPedido)
            .Parameters.AddWithValue("@TDOC", tipoDOC)
            .ExecuteNonQuery()
        End With
        coneccion.Close()
    End Sub

    Sub actualizarFlagRebote(ByVal codPedido As String, tipoDOC As String, flag As String)
        Dim conex As New Conexion
        Dim coneccion As SqlConnection = conex.conectar()
        coneccion.Open()
        Dim query As String = "UPDATE Documento_Cliente " &
                              "SET DCI_Rebote = @FLAG " &
                              "WHERE DCI_Codigo = @DCI " &
                              "AND TDOC_Codigo = @TDOC"

        Dim comando As New SqlCommand
        With comando
            .Connection = coneccion
            .CommandText = query
            .Parameters.AddWithValue("@FLAG", flag)
            .Parameters.AddWithValue("@DCI", codPedido)
            .Parameters.AddWithValue("@TDOC", tipoDOC)
            .ExecuteNonQuery()
        End With
        coneccion.Close()
    End Sub

    Function generarCodigoDocumento(tipoDocumento As String) As String
        Dim conex As New Conexion
        Dim coneccion As SqlConnection = conex.conectar()
        coneccion.Open()
        Dim query As String = "SELECT COUNT(*) AS Contar " &
                              "FROM Documento_Cliente " &
                              "WHERE TDOC_Codigo = '" & tipoDocumento & "'"

        Dim adapter As New SqlDataAdapter(query, coneccion)
        Dim ds As New DataSet
        adapter.Fill(ds)

        coneccion.Close()

        Dim numero As Integer = CInt(ds.Tables(0).Rows(0)("Contar").ToString.Trim) + 1

        Return numero.ToString
    End Function

    Sub registrarDocumentoRebote(codDocumento As String, codPedido As String, tipRebote As String, codMotivo As String, obsMotivo As String)
        Dim conex As New Conexion
        Dim coneccion As SqlConnection = conex.conectar()
        coneccion.Open()

        Dim query As String = "INSERT INTO Documento_Cliente " &
                              "SELECT CIA,SUC_Codigo,'R',@COD,GETDATE(),CLI_Codigo,VEN_Codigo,MON_Codigo, " &
                              "TDO_Codigo,CNV_Codigo,FOP_Codigo,AGE_Codigo,NULL,NULL,NULL,NULL,NULL, " &
                              "NULL,NULL,NULL,DCI_DireccionEntrega,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'02',NULL,@TRB,NULL,NULL,@MOT,@OBSMOT,NULL,NULL,NULL,NULL,NULL " &
                              "FROM Documento_Cliente " &
                              "WHERE DCI_Codigo = @DOC"

        Dim comando As New SqlCommand
        With comando
            .Connection = coneccion
            .CommandText = query
            .Parameters.AddWithValue("@COD", codDocumento)
            .Parameters.AddWithValue("@DOC", codPedido)
            .Parameters.AddWithValue("@TRB", tipRebote)
            .Parameters.AddWithValue("@MOT", codMotivo)
            .Parameters.AddWithValue("@OBSMOT", obsMotivo)
            .ExecuteNonQuery()
        End With
        coneccion.Close()
    End Sub

    Function listarEstadosPedidos() As DataSet
        Dim conex As New Conexion
        Dim coneccion As SqlConnection = conex.conectar()
        coneccion.Open()
        Dim query As String = "SELECT e.EST_Codigo,e.EST_Descripcion, " &
                              "COUNT(dc.DCI_Codigo) AS CantPedidos " &
                              "FROM Estado e " &
                              "LEFT JOIN Documento_Cliente dc " &
                              "ON dc.DCI_Estado = e.EST_Codigo " &
                              "WHERE e.EST_Activo = '1' AND  e.EST_Almacen = '1' " &
                              "GROUP BY e.EST_Codigo,e.EST_Descripcion"

        Dim adapter As New SqlDataAdapter(query, coneccion)
        Dim ds As New DataSet
        adapter.Fill(ds)

        coneccion.Close()

        Return ds
    End Function

    Function listarPedidosXEstado(estado As String) As DataSet
        Dim conex As New Conexion
        Dim coneccion As SqlConnection = conex.conectar()
        coneccion.Open()
        Dim query As String = "SELECT dc.DCI_Codigo, c.CLI_Descripcion, e.EST_Abreviatura, tabla1.Cantidad AS CantItems, dcb.BAL_Codigo AS Balanza, " &
                              "CAST(tabla1.Realizados * 100 / tabla1.Cantidad AS VARCHAR(10)) + ' %' AS Avance, " &
                              "CAST(tabla2.TAsignacion / 60 AS VARCHAR(15)) + ' h. ' + CAST(tabla2.TAsignacion % 60 AS VARCHAR(15)) + ' min.' AS TAsignacion, " &
                              "CAST(tabla2.TPicking / 60 AS VARCHAR(15)) + ' h. ' + CAST(tabla2.TPicking % 60 AS VARCHAR(15)) + ' min.' AS TPicking, " &
                              "CAST(tabla2.TStandBy / 60 AS VARCHAR(15)) + ' h. ' + CAST(tabla2.TStandBy % 60 AS VARCHAR(15)) + ' min.' AS TStandBy, " &
                              "CAST(tabla2.TAsignador / 60 AS VARCHAR(15)) + ' h. ' + CAST(tabla2.TAsignador % 60 AS VARCHAR(15)) + ' min.' AS TAsignador " &
                              "FROM Documento_Cliente dc " &
                              "INNER JOIN Cliente c " &
                              "ON c.CLI_Codigo = dc.CLI_Codigo AND c.CLI_Activo = '1' " &
                              "INNER JOIN Estado e " &
                              "ON e.EST_Codigo = dc.DCI_Estado AND e.EST_Activo = '1' " &
                              "LEFT JOIN Documento_Cliente_Balanza dcb " &
                              "ON dcb.CIA = dc.CIA AND dcb.SUC_Codigo = dc.SUC_Codigo AND dcb.TDOC_Codigo = dc.TDOC_Codigo AND dcb.DCI_Codigo = dc.DCI_Codigo AND dcb.DCB_Activo = '1' " &
                              "INNER JOIN (SELECT vdc.CIA_Cliente, vdc.SUC_Cliente, vdc.TDOC_Cliente, vdc.DCI_Codigo, SUM(ve1.TAsignacion) AS TAsignacion, " &
                              "SUM(ISNULL(ve2.TPicking, 0)) AS TPicking, SUM(ISNULL(ve3.TStandBy,0)) AS TStandBy, SUM(ISNULL(ve4.TAsignador,0)) AS TAsignador " &
                              "FROM VW_VistaDocumentoClientePickeador vdc " &
                              "LEFT JOIN VW_VistaEventos ve1 " &
                              "ON ve1.CIA = vdc.CIA_Almacen AND ve1.SUC_Codigo = vdc.SUC_Almacen AND ve1.TDOC_Codigo = vdc.TDOC_Almacen " &
                              "AND ve1.DCA_Codigo = vdc.DCA_Codigo AND ve1.DAP_Codigo = vdc.DAP_Codigo AND ve1.EVE_Codigo = '01' " &
                              "LEFT JOIN VW_VistaEventos ve2 " &
                              "ON ve2.CIA = vdc.CIA_Almacen AND ve2.SUC_Codigo = vdc.SUC_Almacen AND ve2.TDOC_Codigo = vdc.TDOC_Almacen " &
                              "AND ve2.DCA_Codigo = vdc.DCA_Codigo AND ve2.DAP_Codigo = vdc.DAP_Codigo AND ve2.EVE_Codigo = '05' " &
                              "LEFT JOIN VW_VistaEventos ve3 " &
                              "ON ve3.CIA = vdc.CIA_Almacen AND ve3.SUC_Codigo = vdc.SUC_Almacen AND ve3.TDOC_Codigo = vdc.TDOC_Almacen " &
                              "AND ve3.DCA_Codigo = vdc.DCA_Codigo AND ve3.DAP_Codigo = vdc.DAP_Codigo AND ve3.EVE_Codigo = '03' " &
                              "LEFT JOIN VW_VistaEventos ve4 " &
                              "ON ve4.CIA = vdc.CIA_Almacen AND ve4.SUC_Codigo = vdc.SUC_Almacen AND ve4.TDOC_Codigo = vdc.TDOC_Almacen " &
                              "AND ve4.DCA_Codigo = vdc.DCA_Codigo AND ve4.DAP_Codigo = vdc.DAP_Codigo AND ve4.EVE_Codigo = '07' " &
                              "GROUP BY vdc.CIA_Cliente, vdc.SUC_Cliente, vdc.TDOC_Cliente, vdc.DCI_Codigo) AS tabla2 " &
                              "ON tabla2.CIA_Cliente = dc.CIA AND tabla2.SUC_Cliente = dc.SUC_Codigo AND tabla2.TDOC_Cliente = dc.TDOC_Codigo AND tabla2.DCI_Codigo = dc.DCI_Codigo " &
                              "INNER JOIN (SELECT dcad.CIA_Cliente, dcad.SUC_Cliente, TDOC_Cliente, dcad.DCI_Codigo, COUNT(dad.DAD_Codigo) AS Cantidad, " &
                              "SUM(CASE WHEN dadp.DADP_FechaFin IS NULL THEN 0 ELSE 1 END) AS Realizados " &
                              "FROM Documento_Cliente_Almacen_Detalle dcad " &
                              "INNER JOIN Documento_Almacen_Detalle dad " &
                              "ON dad.CIA = dcad.CIA_Almacen AND dad.SUC = dcad.SUC_Almacen AND dad.TDOC_Codigo = dcad.TDOC_Almacen " &
                              "AND dad.DCA_Codigo = dcad.DCA_Codigo AND dad.DAD_Codigo = dcad.DAD_Codigo AND dad.DAD_Activo = '1' " &
                              "INNER JOIN Documento_Almacen_Detalle_Pickeador dadp " &
                              "ON dadp.CIA = dad.CIA AND dadp.SUC_Codigo = dad.SUC AND dadp.TDOC_Codigo = dad.TDOC_Codigo " &
                              "AND dadp.DCA_Codigo = dad.DCA_Codigo AND dadp.DAD_Codigo = dad.DAD_Codigo AND dadp.DADP_Activo = '1' " &
                              "WHERE dcad.DCAD_Activo = '1' " &
                              "GROUP BY dcad.CIA_Cliente, dcad.SUC_Cliente, TDOC_Cliente, dcad.DCI_Codigo) AS tabla1 " &
                              "ON tabla1.CIA_Cliente = dc.CIA AND tabla1.SUC_Cliente = dc.SUC_Codigo AND tabla1.TDOC_Cliente = dc.TDOC_Codigo AND tabla1.DCI_Codigo = dc.DCI_Codigo " &
                              "WHERE dc.DCI_Estado = @EST " &
                              "GROUP BY dc.DCI_Codigo, c.CLI_Descripcion, e.EST_Abreviatura, tabla1.Cantidad, dcb.BAL_Codigo, " &
                              "tabla2.TAsignacion, tabla2.TPicking, tabla2.TStandBy, tabla2.TAsignador, tabla1.Realizados"

        Dim comando As New SqlCommand(query, coneccion)
        With comando
            .Parameters.AddWithValue("@EST", estado)
        End With
        Dim adapter As New SqlDataAdapter(comando)
        Dim ds As New DataSet
        adapter.Fill(ds)

        coneccion.Close()

        Return ds
    End Function

    Function listarItemsPedidos(codPedido As String) As DataSet
        Dim conex As New Conexion
        Dim coneccion As SqlConnection = conex.conectar()
        coneccion.Open()
        Dim query As String = "SELECT da.PICK_Codigo, u.USU_Descripcion AS Pickeador, " &
                              "COUNT(DISTINCT(da.DAD_Codigo)) AS CantAsignada, " &
                              "SUM(ISNULL((CASE WHEN da.DADP_FechaFin IS NOT NULL THEN 1 ELSE 0 END),0)) AS CantAtendida, " &
                              "CAST(SUM(ISNULL((CASE WHEN da.DADP_FechaFin IS NOT NULL THEN 1 ELSE 0 END),0)) / COUNT(da.DAD_Codigo) * 100 AS VARCHAR(10)) + ' %' AS Avance, " &
                              "CAST(ISNULL(tabla.TEfectivo,0) / 60 AS VARCHAR(15)) + ' h. ' + CAST(ISNULL(tabla.TEfectivo,0) % 60 AS VARCHAR(15)) + ' min.' AS TEfectivo, " &
                              "CAST(ISNULL(ve3.TStandBy,0) / 60 AS VARCHAR(15)) + ' h. ' + CAST(ISNULL(ve3.TStandBy,0) % 60 AS VARCHAR(15)) + ' min.' AS TStandBy, " &
                              "CAST((ISNULL(ve2.TPicking,0) - (ISNULL(tabla.TEfectivo,0) + ISNULL(ve3.TStandBy,0))) / 60 AS VARCHAR(15)) + ' h. ' + CAST((ISNULL(ve2.TPicking,0) - (ISNULL(tabla.TEfectivo,0) + ISNULL(ve3.TStandBy,0))) % 60 AS VARCHAR(15)) + ' min.' AS TOtros " &
                              "FROM Documento_Cliente dc " &
                              "INNER JOIN Documento_Cliente_Detalle dcd " &
                              "ON dcd.CIA = dc.CIA AND dcd.SUC = dc.SUC_Codigo AND dcd.TDOC_Codigo = dc.TDOC_Codigo " &
                              "AND dcd.DCI_Codigo = dc.DCI_Codigo AND dcd.DCD_Activo = '1' " &
                              "INNER JOIN Documento_Cliente_Almacen_Detalle dcad " &
                              "ON dcad.CIA_Cliente = dcd.CIA AND dcad.SUC_Cliente = dcd.SUC AND dcad.TDOC_Cliente = dcd.TDOC_Codigo " &
                              "AND dcad.DCI_Codigo = dcd.DCI_Codigo AND dcad.DCD_Codigo = dcd.DCD_Codigo AND dcad.DCAD_Activo = '1' " &
                              "INNER JOIN VW_VistaDocumentoAlmacen da " &
                              "ON da.CIA = dcad.CIA_Almacen AND da.SUC_Codigo = dcad.SUC_Almacen AND da.TDOC_Codigo = dcad.TDOC_Almacen " &
                              "AND da.DCA_Codigo = dcad.DCA_Codigo AND da.DAD_Codigo = dcad.DAD_Codigo " &
                              "INNER JOIN Usuario u " &
                              "ON u.USU_Codigo = da.PICK_Codigo AND u.USU_Activo = '1' " &
                              "LEFT JOIN VW_VistaEventos ve1 " &
                              "ON ve1.CIA = da.CIA AND ve1.SUC_Codigo = da.SUC_Codigo AND ve1.TDOC_Codigo = da.TDOC_Codigo " &
                              "AND ve1.DCA_Codigo = da.DCA_Codigo AND ve1.DAP_Codigo = da.DAP_Codigo AND ve1.EVE_Codigo = '01' " &
                              "LEFT JOIN VW_VistaEventos ve2 " &
                              "ON ve2.CIA = da.CIA AND ve2.SUC_Codigo = da.SUC_Codigo AND ve2.TDOC_Codigo = da.TDOC_Codigo " &
                              "AND ve2.DCA_Codigo = da.DCA_Codigo AND ve2.DAP_Codigo = da.DAP_Codigo AND ve2.EVE_Codigo = '05' " &
                              "LEFT JOIN VW_VistaEventos ve3 " &
                              "ON ve3.CIA = da.CIA AND ve3.SUC_Codigo = da.SUC_Codigo AND ve3.TDOC_Codigo = da.TDOC_Codigo " &
                              "AND ve3.DCA_Codigo = da.DCA_Codigo AND ve3.DAP_Codigo = da.DAP_Codigo AND ve3.EVE_Codigo = '03' " &
                              "INNER JOIN (SELECT dadp.CIA, dadp.SUC_Codigo, dadp.TDOC_Codigo, dadp.DCA_Codigo, dadp.DAP_Codigo, " &
                              "SUM(DATEDIFF(minute, dadp.DADP_FechaInicio, ISNULL(dadp.DADP_FechaFin,GETDATE()))) AS TEfectivo " &
                              "FROM Documento_Almacen_Detalle_Pickeador dadp " &
                              "WHERE dadp.DADP_Activo = '1' " &
                              "GROUP BY dadp.CIA, dadp.SUC_Codigo, dadp.TDOC_Codigo, dadp.DCA_Codigo, dadp.DAP_Codigo) AS tabla " &
                              "ON tabla.CIA = da.CIA AND tabla.SUC_Codigo = da.SUC_Codigo AND tabla.TDOC_Codigo = da.TDOC_Codigo " &
                              "AND tabla.DCA_Codigo = da.DCA_Codigo AND tabla.DAP_Codigo = da.DAP_Codigo " &
                              "WHERE dc.DCI_Codigo = @DCI " &
                              "GROUP BY da.PICK_Codigo, u.USU_Descripcion, ve2.TPicking, ve3.TStandBy, tabla.TEfectivo"

        Dim comando As New SqlCommand(query, coneccion)
        With comando
            .Parameters.AddWithValue("@DCI", codPedido)
        End With
        Dim adapter As New SqlDataAdapter(comando)
        Dim ds As New DataSet
        adapter.Fill(ds)

        coneccion.Close()

        Return ds
    End Function

    Function listarBultosPedidos(codPedido) As DataSet
        Dim conex As New Conexion
        Dim coneccion As SqlConnection = conex.conectar()
        coneccion.Open()
        Dim query As String = "SELECT tc.TCA_Descripcion AS Descripcion, " &
                              "dcb.DCB_Cantidad AS Cantidad, " &
                              "dcb.DCB_PesoNeto AS PesoNeto, " &
                              "dcb.DCB_PesoBruto AS PesoBruto " &
                              "FROM Documento_Cliente_Bulto dcb " &
                              "INNER JOIN Tipo_Caja tc " &
                              "ON tc.TCA_Codigo = dcb.TCA_Caja AND tc.TCA_Activo = '1' " &
                              "WHERE dcb.DCI_Codigo = @DCI AND dcb.DCB_Activo = '1' " &
                              "UNION " &
                              "SELECT a.ART_Descripcion AS Descripcion, " &
                              "dad.DAD_Cantidad AS Cantidad, " &
                              "'0.00','0.00' " &
                              "FROM Documento_Cliente_Almacen_Detalle dcad " &
                              "INNER JOIN Documento_Almacen_Detalle dad " &
                              "ON dad.CIA = dcad.CIA_Cliente AND dad.SUC = dcad.SUC_Cliente AND dad.TDOC_Codigo = dcad.TDOC_Cliente " &
                              "AND dad.DCA_Codigo = dcad.DCA_Codigo AND dad.DAD_Codigo = dcad.DAD_Codigo AND dad.DAD_Activo = '1' " &
                              "INNER JOIN Articulo a " &
                              "ON a.ART_Codigo = dad.ART_Codigo AND a.ART_Largo = '1' AND a.ART_Activo = '1' " &
                              "WHERE dcad.DCI_Codigo = @DCI AND dcad.DCAD_Activo = '1'"

        Dim comando As New SqlCommand(query, coneccion)
        With comando
            .Parameters.AddWithValue("@DCI", codPedido)
        End With
        Dim adapter As New SqlDataAdapter(comando)
        Dim ds As New DataSet
        adapter.Fill(ds)

        coneccion.Close()

        Return ds
    End Function

    Function obtenerDatosPedido(codPedido As String) As DataSet
        Dim conex As New Conexion
        Dim coneccion As SqlConnection = conex.conectar()
        coneccion.Open()
        Dim query As String = "SELECT c.CLI_Descripcion, ce.CLEN_Direccion + ' / ' + " &
                              "dbo.FNX_ObtenerAgrupado(LTRIM(RTRIM(d1.DIS_Descripcion)),LTRIM(RTRIM(p1.PRO_Descripcion)),LTRIM(RTRIM(de1.DEP_Descripcion))) AS Direccion1, " &
                              "at.AGE_Descripcion, ats.ATS_Descripcion, ats.ATS_Direccion + ' / ' + " &
                              "dbo.FNX_ObtenerAgrupado(LTRIM(RTRIM(d2.DIS_Descripcion)),LTRIM(RTRIM(p2.PRO_Descripcion)),LTRIM(RTRIM(de2.DEP_Descripcion))) AS Direccion2, " &
                              "dc.DCI_Estado, e.EST_Descripcion " &
                              "FROM Documento_Cliente dc " &
                              "LEFT JOIN Cliente c " &
                              "ON c.CLI_Codigo = dc.CLI_Codigo AND c.CLI_Activo = '1' " &
                              "LEFT JOIN ClienteEntrega ce " &
                              "ON ce.CLEN_Codigo = dc.CLEN_Codigo AND ce.CLI_Codigo = dc.CLI_Codigo AND ce.CLEN_Activo = '1' " &
                              "LEFT JOIN Distrito d1 " &
                              "ON d1.DIS_Codigo = ce.DIS_Codigo AND d1.DIS_Activo = '1' " &
                              "LEFT JOIN Provincia p1 " &
                              "ON p1.PRO_Codigo = d1.PRO_Codigo AND p1.PRO_Activo = '1' " &
                              "LEFT JOIN Departamento de1 " &
                              "ON de1.DEP_Codigo = p1.DEP_Codigo AND de1.DEP_Activo = '1' " &
                              "LEFT JOIN AgenciaTransporte at " &
                              "ON at.AGE_Codigo = dc.AGE_Codigo AND at.AGE_Activo = '1' " &
                              "LEFT JOIN AgenciaTransporteSucursal ats " &
                              "ON ats.ATS_Codigo = dc.ATS_Codigo AND ats.ATS_Activo = '1' " &
                              "LEFT JOIN Distrito d2 " &
                              "ON d2.DIS_Codigo = ats.DIS_Codigo AND d2.DIS_Activo = '1' " &
                              "LEFT JOIN Provincia p2 " &
                              "ON p2.PRO_Codigo = d2.PRO_Codigo AND p2.PRO_Activo = '1' " &
                              "LEFT JOIN Departamento de2 " &
                              "ON de2.DEP_Codigo = p2.DEP_Codigo AND de2.DEP_Activo = '1' " &
                              "LEFT JOIN Estado e " &
                              "ON e.EST_Codigo = dc.DCI_Estado AND e.EST_Activo = '1' " &
                              "WHERE dc.DCI_Codigo = @DCI"

        Dim comando As New SqlCommand(query, coneccion)
        With comando
            .Parameters.AddWithValue("@DCI", codPedido)
        End With
        Dim adapter As New SqlDataAdapter(comando)
        Dim ds As New DataSet
        adapter.Fill(ds)

        coneccion.Close()

        Return ds
    End Function

    Sub actualizarFechaInicioCelda(ByVal codPedido As String, tipoDOC As String)
        Dim conex As New Conexion
        Dim coneccion As SqlConnection = conex.conectar()
        coneccion.Open()
        Dim query As String = "UPDATE Documento_Cliente " &
                              "SET DCI_InicioCelda = GETDATE() " &
                              "WHERE DCI_Codigo = @codigo " &
                              "AND TDOC_Codigo = @tipo"

        Dim comando As New SqlCommand
        With comando
            .Connection = coneccion
            .CommandText = query
            .Parameters.AddWithValue("@codigo", codPedido)
            .Parameters.AddWithValue("@tipo", tipoDOC)
            .ExecuteNonQuery()
        End With
        coneccion.Close()
    End Sub

    Function listarMotivosRebote(codMotivo As String) As DataSet
        IIf(codMotivo = "---", codMotivo = "", codMotivo = Nothing)
        Dim conex As New Conexion
        Dim coneccion As SqlConnection = conex.conectar()
        coneccion.Open()
        Dim query As String = "SELECT MOR_Codigo,MOR_Descripcion " &
                              "FROM MotivoRebote " &
                              "WHERE MOR_Codigo = '" & codMotivo & "' AND MOR_Activo = '1' " &
                              "UNION " &
                              "SELECT MOR_Codigo,MOR_Descripcion " &
                              "FROM MotivoRebote " &
                              "WHERE MOR_Codigo <> '" & codMotivo & "' AND MOR_Activo = '1'"


        Dim adapter As New SqlDataAdapter(query, coneccion)
        Dim ds As New DataSet
        adapter.Fill(ds)

        If codMotivo = "" Then
            Dim dt As New DataTable
            dt = ds.Tables(0)
            Dim row As DataRow = dt.NewRow()
            row.Item("MOR_Codigo") = 0
            row.Item("MOR_Descripcion") = "Seleccionar..."
            dt.Rows.InsertAt(row, 0)
        End If

        coneccion.Close()

        Return ds
    End Function

    Sub registrarDocumentoCliente(pedido As Pedido)
        Dim conex As New Conexion
        Dim coneccion As SqlConnection = conex.conectar()
        coneccion.Open()

        Dim query As String = "INSERT INTO Documento_Cliente(CIA,SUC_Codigo,TDOC_Codigo,DCI_Codigo,DCI_Fecha,CLI_Codigo,VEN_Codigo,MON_Codigo, " &
                              "TDO_Codigo,CNV_Codigo,FOP_Codigo,AGE_Codigo,DCI_TotalBruto,DCI_TotalDsct,DCI_TotalNeto,DCI_IGV,DCI_Total, " &
                              "DCI_UltModificacion,DCI_Observaciones,DCI_ObsCredito,DCI_DireccionEntrega,CLEN_Codigo,DCI_Estado,DCI_FechaEntrega, " &
                              "DCI_Oficina,DCI_Muestra,DCI_ConfPersona,DCI_RecPersona,ATS_Codigo,BAN_Codigo,DCI_NDeposito,LIS_Codigo,DCI_RazonCot,USU_Codigo) " &
                              "VALUES(@CIA,@SUC,@TDOC,@DCI,GETDATE(),@CLI,@VEN,@MON,@TDO,@CNV,@FOP,@AGE,@TOTB,@TOTD,@TOTN,@IGV,@TOT,GETDATE(),@OBS,@OBSC,@DIE, " &
                              "@CLEN,@EST,@FECE,@OFI,@MUE,@CONF,@REC,@ATS,@BAN,@NRO,@LIS,@RAZ,@USU)"

        Dim comando As New SqlCommand
        With comando
            .Connection = coneccion
            .CommandText = query
            .Parameters.AddWithValue("@CIA", pedido.CIA)
            .Parameters.AddWithValue("@SUC", pedido.SUC_Codigo)
            .Parameters.AddWithValue("@TDOC", pedido.TDOC_Codigo)
            .Parameters.AddWithValue("@DCI", pedido.DCI_Codigo)
            .Parameters.AddWithValue("@CLI", IIf(pedido.CLI_Codigo = "", DBNull.Value, pedido.CLI_Codigo))
            .Parameters.AddWithValue("@VEN", IIf(pedido.VEN_Codigo Is Nothing, DBNull.Value, pedido.VEN_Codigo))
            .Parameters.AddWithValue("@MON", IIf(pedido.MON_Codigo Is Nothing, DBNull.Value, pedido.MON_Codigo))
            .Parameters.AddWithValue("@TDO", IIf(pedido.TDO_Codigo Is Nothing, DBNull.Value, pedido.TDO_Codigo))
            .Parameters.AddWithValue("@CNV", IIf(pedido.CNV_Codigo Is Nothing, DBNull.Value, pedido.CNV_Codigo))
            .Parameters.AddWithValue("@FOP", IIf(pedido.FOP_Codigo Is Nothing, DBNull.Value, pedido.FOP_Codigo))
            .Parameters.AddWithValue("@AGE", IIf(pedido.AGE_Codigo Is Nothing, DBNull.Value, pedido.AGE_Codigo))
            .Parameters.AddWithValue("@TOTB", pedido.DCI_TotalBruto)
            .Parameters.AddWithValue("@TOTD", pedido.DCI_TotaDsct)
            .Parameters.AddWithValue("@TOTN", pedido.DCI_TotalNeto)
            .Parameters.AddWithValue("@IGV", pedido.DCI_IGV)
            .Parameters.AddWithValue("@TOT", pedido.DCI_Total)
            .Parameters.AddWithValue("@OBS", IIf(pedido.DCI_Observaciones Is Nothing, DBNull.Value, pedido.DCI_Observaciones))
            .Parameters.AddWithValue("@OBSC", IIf(pedido.DCI_OBSCredito Is Nothing, DBNull.Value, pedido.DCI_OBSCredito))
            .Parameters.AddWithValue("@DIE", IIf(pedido.DCI_DireccionEntrega Is Nothing, DBNull.Value, pedido.DCI_DireccionEntrega))
            .Parameters.AddWithValue("@CLEN", IIf(pedido.CLEN_Codigo Is Nothing, DBNull.Value, pedido.CLEN_Codigo))
            .Parameters.AddWithValue("@EST", pedido.DCI_Estado)
            .Parameters.AddWithValue("@FECE", IIf(pedido.FEC_Entrega Is Nothing, DBNull.Value, pedido.FEC_Entrega))
            .Parameters.AddWithValue("@OFI", IIf(pedido.DCI_Oficina Is Nothing, DBNull.Value, pedido.DCI_Oficina))
            .Parameters.AddWithValue("@MUE", IIf(pedido.DCI_Muestra Is Nothing, DBNull.Value, pedido.DCI_Muestra))
            .Parameters.AddWithValue("@CONF", IIf(pedido.PERS_Confirma Is Nothing, DBNull.Value, pedido.PERS_Confirma))
            .Parameters.AddWithValue("@REC", IIf(pedido.PERS_Recibe Is Nothing, DBNull.Value, pedido.PERS_Recibe))
            .Parameters.AddWithValue("@ATS", IIf(pedido.ATS_Codigo Is Nothing, DBNull.Value, pedido.ATS_Codigo))
            .Parameters.AddWithValue("@BAN", IIf(pedido.BAN_Codigo Is Nothing, DBNull.Value, pedido.BAN_Codigo))
            .Parameters.AddWithValue("@NRO", IIf(pedido.NRO_Deposito Is Nothing, DBNull.Value, pedido.NRO_Deposito))
            .Parameters.AddWithValue("@LIS", IIf(pedido.LIS_Codigo Is Nothing, DBNull.Value, pedido.LIS_Codigo))
            .Parameters.AddWithValue("@RAZ", IIf(pedido.CLI_Codigo <> "", DBNull.Value, pedido.DCI_RazonCot))
            .Parameters.AddWithValue("@USU", pedido.USU_Codigo)
            .ExecuteNonQuery()
        End With
        coneccion.Close()
    End Sub

    Sub actualizarDocumentoCliente(pedido As Pedido)
        Dim conex As New Conexion
        Dim coneccion As SqlConnection = conex.conectar()
        coneccion.Open()

        Dim query As String = "UPDATE Documento_Cliente " &
                              "SET CLI_Codigo = @CLI, VEN_Codigo = @VEN, MON_Codigo = @MON, " &
                              "TDO_Codigo = @TDO, CNV_Codigo = @CNV, FOP_Codigo = @FOP, AGE_Codigo = @AGE, " &
                              "DCI_TotalBruto = @TOTB, DCI_TotalDsct = @TOTD, DCI_TotalNeto = @TOTN, " &
                              "DCI_IGV = @IGV, DCI_Total = @TOT, DCI_UltModificacion = GETDATE(), " &
                              "DCI_Observaciones = @OBS, DCI_ObsCredito = @OBSC, DCI_DireccionEntrega = @DIE, " &
                              "CLEN_Codigo = @CLEN, DCI_Estado = @EST, DCI_FechaEntrega = @FECE, DCI_Oficina = @OFI, " &
                              "DCI_Muestra = @MUE, DCI_ConfPersona = @CONF, DCI_RecPersona = @REC, ATS_Codigo = @ATS, " &
                              "BAN_Codigo = @BAN, DCI_NDeposito = @NRO, LIS_Codigo = @LIS, DCI_RazonCot = @RAZ, USU_Codigo = @USU " &
                              "WHERE CIA = @CIA AND SUC_Codigo = @SUC AND TDOC_Codigo = @TDOC AND DCI_Codigo = @DCI"

        Dim comando As New SqlCommand
        With comando
            .Connection = coneccion
            .CommandText = query
            .Parameters.AddWithValue("@CLI", IIf(pedido.CLI_Codigo = "", DBNull.Value, pedido.CLI_Codigo))
            .Parameters.AddWithValue("@VEN", IIf(pedido.VEN_Codigo Is Nothing, DBNull.Value, pedido.VEN_Codigo))
            .Parameters.AddWithValue("@MON", IIf(pedido.MON_Codigo Is Nothing, DBNull.Value, pedido.MON_Codigo))
            .Parameters.AddWithValue("@TDO", IIf(pedido.TDO_Codigo Is Nothing, DBNull.Value, pedido.TDO_Codigo))
            .Parameters.AddWithValue("@CNV", IIf(pedido.CNV_Codigo Is Nothing, DBNull.Value, pedido.CNV_Codigo))
            .Parameters.AddWithValue("@FOP", IIf(pedido.FOP_Codigo Is Nothing, DBNull.Value, pedido.FOP_Codigo))
            .Parameters.AddWithValue("@AGE", IIf(pedido.AGE_Codigo Is Nothing, DBNull.Value, pedido.AGE_Codigo))
            .Parameters.AddWithValue("@TOTB", pedido.DCI_TotalBruto)
            .Parameters.AddWithValue("@TOTD", pedido.DCI_TotaDsct)
            .Parameters.AddWithValue("@TOTN", pedido.DCI_TotalNeto)
            .Parameters.AddWithValue("@IGV", pedido.DCI_IGV)
            .Parameters.AddWithValue("@TOT", pedido.DCI_Total)
            .Parameters.AddWithValue("@OBS", IIf(pedido.DCI_Observaciones Is Nothing, DBNull.Value, pedido.DCI_Observaciones))
            .Parameters.AddWithValue("@OBSC", IIf(pedido.DCI_OBSCredito Is Nothing, DBNull.Value, pedido.DCI_OBSCredito))
            .Parameters.AddWithValue("@DIE", IIf(pedido.DCI_DireccionEntrega Is Nothing, DBNull.Value, pedido.DCI_DireccionEntrega))
            .Parameters.AddWithValue("@CLEN", IIf(pedido.CLEN_Codigo Is Nothing, DBNull.Value, pedido.CLEN_Codigo))
            .Parameters.AddWithValue("@EST", pedido.DCI_Estado)
            .Parameters.AddWithValue("@FECE", IIf(pedido.FEC_Entrega Is Nothing, DBNull.Value, pedido.FEC_Entrega))
            .Parameters.AddWithValue("@OFI", IIf(pedido.DCI_Oficina Is Nothing, DBNull.Value, pedido.DCI_Oficina))
            .Parameters.AddWithValue("@MUE", IIf(pedido.DCI_Muestra Is Nothing, DBNull.Value, pedido.DCI_Muestra))
            .Parameters.AddWithValue("@CONF", IIf(pedido.PERS_Confirma Is Nothing, DBNull.Value, pedido.PERS_Confirma))
            .Parameters.AddWithValue("@REC", IIf(pedido.PERS_Recibe Is Nothing, DBNull.Value, pedido.PERS_Recibe))
            .Parameters.AddWithValue("@ATS", IIf(pedido.ATS_Codigo Is Nothing, DBNull.Value, pedido.ATS_Codigo))
            .Parameters.AddWithValue("@BAN", IIf(pedido.BAN_Codigo Is Nothing, DBNull.Value, pedido.BAN_Codigo))
            .Parameters.AddWithValue("@NRO", IIf(pedido.NRO_Deposito Is Nothing, DBNull.Value, pedido.NRO_Deposito))
            .Parameters.AddWithValue("@LIS", IIf(pedido.LIS_Codigo Is Nothing, DBNull.Value, pedido.LIS_Codigo))
            .Parameters.AddWithValue("@RAZ", IIf(pedido.CLI_Codigo <> "", DBNull.Value, pedido.DCI_RazonCot))
            .Parameters.AddWithValue("@USU", pedido.USU_Codigo)
            .Parameters.AddWithValue("@CIA", pedido.CIA)
            .Parameters.AddWithValue("@SUC", pedido.SUC_Codigo)
            .Parameters.AddWithValue("@TDOC", pedido.TDOC_Codigo)
            .Parameters.AddWithValue("@DCI", pedido.DCI_Codigo)
            .ExecuteNonQuery()
        End With
        coneccion.Close()
    End Sub

    Function obtenerDocumentoCliente(ciaCodigo As String, sucCodigo As String, tdocCodigo As String, codPedido As String) As Pedido
        Dim pedido As Pedido = Nothing
        Dim conex As New Conexion
        Dim coneccion As SqlConnection = conex.conectar()
        coneccion.Open()
        Dim query As String = "SELECT CIA,SUC_Codigo,TDOC_Codigo,DCI_Codigo,CONVERT(VARCHAR(15),DCI_Fecha,103) AS Fecha,dc.CLI_Codigo,dc.VEN_Codigo,MON_Codigo, " &
                              "TDO_Codigo,dc.CNV_Codigo,FOP_Codigo,AGE_Codigo,DCI_TotalBruto,DCI_TotalDsct,DCI_TotalNeto,DCI_IGV,DCI_Total, " &
                              "DCI_UltModificacion,DCI_Observaciones,DCI_ObsCredito,DCI_DireccionEntrega,CLEN_Codigo,DCI_Estado,DCI_FechaEntrega, " &
                              "DCI_Oficina,DCI_Muestra,DCI_ConfPersona,DCI_RecPersona,ATS_Codigo,BAN_Codigo,DCI_NDeposito,LIS_Codigo,DCI_RazonCot,c.CLI_Marca, USU_Codigo " &
                              "FROM Documento_Cliente dc " &
                              "LEFT JOIN Cliente c " &
                              "ON c.CLI_Codigo = dc.CLI_Codigo AND c.CLI_Activo = '1' " &
                              "WHERE CIA = @CIA AND SUC_Codigo = @SUC AND TDOC_Codigo = @TDOC AND DCI_Codigo = @DCI AND DCI_Estado <> '17'"

        Dim comando As New SqlCommand(query, coneccion)
        With comando
            .Parameters.AddWithValue("@CIA", ciaCodigo)
            .Parameters.AddWithValue("@SUC", sucCodigo)
            .Parameters.AddWithValue("@TDOC", tdocCodigo)
            .Parameters.AddWithValue("@DCI", codPedido)
        End With
        Dim adapter As New SqlDataAdapter(comando)
        Dim ds As New DataSet
        adapter.Fill(ds)

        If ds.Tables(0).Rows.Count > 0 Then
            pedido = New Pedido(ds.Tables(0).Rows(0)("CIA").ToString.Trim, ds.Tables(0).Rows(0)("SUC_Codigo").ToString.Trim, ds.Tables(0).Rows(0)("TDOC_Codigo").ToString.Trim, ds.Tables(0).Rows(0)("DCI_Codigo").ToString.Trim, ds.Tables(0).Rows(0)("CLI_Codigo").ToString.Trim,
                                ds.Tables(0).Rows(0)("VEN_Codigo").ToString.Trim, ds.Tables(0).Rows(0)("MON_Codigo").ToString.Trim, ds.Tables(0).Rows(0)("TDO_Codigo").ToString.Trim, ds.Tables(0).Rows(0)("CNV_Codigo").ToString.Trim, ds.Tables(0).Rows(0)("FOP_Codigo").ToString.Trim,
                                ds.Tables(0).Rows(0)("AGE_Codigo").ToString.Trim, CDec(ds.Tables(0).Rows(0)("DCI_TotalBruto").ToString.Trim), CDec(ds.Tables(0).Rows(0)("DCI_TotalDsct").ToString.Trim), CDec(ds.Tables(0).Rows(0)("DCI_TotalNeto").ToString.Trim),
                                CDec(ds.Tables(0).Rows(0)("DCI_IGV").ToString.Trim), CDec(ds.Tables(0).Rows(0)("DCI_Total").ToString.Trim), ds.Tables(0).Rows(0)("DCI_Observaciones").ToString.Trim, ds.Tables(0).Rows(0)("DCI_ObsCredito").ToString.Trim,
                                ds.Tables(0).Rows(0)("DCI_DireccionEntrega").ToString.Trim, ds.Tables(0).Rows(0)("DCI_Estado").ToString.Trim, ds.Tables(0).Rows(0)("CLEN_Codigo").ToString.Trim, ds.Tables(0).Rows(0)("DCI_FechaEntrega").ToString.Trim,
                                ds.Tables(0).Rows(0)("DCI_Oficina").ToString.Trim, ds.Tables(0).Rows(0)("DCI_Muestra").ToString.Trim, ds.Tables(0).Rows(0)("DCI_ConfPersona").ToString.Trim, ds.Tables(0).Rows(0)("DCI_RecPersona").ToString.Trim, ds.Tables(0).Rows(0)("ATS_Codigo").ToString.Trim,
                                ds.Tables(0).Rows(0)("BAN_Codigo").ToString.Trim, ds.Tables(0).Rows(0)("DCI_NDeposito").ToString.Trim, ds.Tables(0).Rows(0)("LIS_Codigo").ToString.Trim, ds.Tables(0).Rows(0)("DCI_RazonCot").ToString.Trim, ds.Tables(0).Rows(0)("Fecha").ToString.Trim, ds.Tables(0).Rows(0)("CLI_Marca").ToString.Trim, ds.Tables(0).Rows(0)("USU_Codigo").ToString.Trim)
        End If

        Return pedido
    End Function

    Function sincronizarDocumentosClientes() As Boolean
        Dim valor As Boolean = False
        Dim conex As New Conexion
        Dim coneccion As SqlConnection = conex.conectar

        Try
            coneccion.Open()
            Dim query As String = ""

        Catch ex As Exception
            valor = False
        Finally
            coneccion.Close()
        End Try

        Return valor
    End Function

End Class
